package ut.data;
import ut.util.Cache;
import java.util.Date;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
public class User{
	public static final int SYSTEM_ID = 0;
	public static final int MAX_FAVORITE = 20;
	public static int join(String email, String password, String name){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			rs = stmt.executeQuery("select id from user where email='" + email + "'");
			if(rs.next()){
				return 0;
			}
			stmt.executeUpdate("insert into user(email,password,name,login_time,join_time) values('" + email + "','" + password + "','" + name + "',now(),now())", Statement.RETURN_GENERATED_KEYS);
			rs = stmt.getGeneratedKeys();
			int id;
			if(rs.next()){
				id = rs.getInt(1);
			}else{
				return 0;
			}
			stmt.executeUpdate("insert into record(id) values(" + id + ")");
			con.commit();
			// 若資料庫處理成功，進行驗證信件發送
			ut.service.Mail.sendVerifyEmail(id, email, name, ut.util.MD5.digestToHex(id + " at ultimate translation (!@$^#!^*&)"));
			return 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	public static int login(String email, String password, javax.servlet.http.HttpSession session){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from user where email='" + email + "' and password='" + password + "'");
			if(rs.next()){
				User user = new User(rs.getInt("id"), rs.getString("email"), rs.getString("name"), rs.getString("phone"), 
					rs.getInt("verify"), rs.getBoolean("notify"), rs.getInt("money"), rs.getInt("fee"), rs.getTimestamp("login_time"), rs.getTimestamp("join_time"));
				stmt.executeUpdate("update user set login_time=now() where email='" + email + "'");
				session.setAttribute("user", user);
				return 1;
			}else{
				session.removeAttribute("user");
				return 0;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	public static String getUserName(int id){
		if(id == User.SYSTEM_ID){
			return "站務系統";
		}
		String name;
		if((name = Cache.getUserName(id)) != null){
			return name;
		}
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select name from user where id=" + id);
			if(rs.next()){
				// 加入快取
				name = rs.getString("name");
				Cache.cacheUserName(id, name);
				return name;
			}else{
				
				return null;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	public static User getUser(int id){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from user where id=" + id);
			if(rs.next()){
				return new User(rs.getInt("id"), rs.getString("email"), rs.getString("name"), rs.getString("phone"), rs.getInt("verify"), rs.getBoolean("notify"), rs.getInt("money"), rs.getInt("fee"), rs.getTimestamp("login_time"), rs.getTimestamp("join_time"));
			}else{
				return null;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	public static boolean changePassword(int id, String oldPassword, String newPassword){
		return (Database.executeUpdate("update user set password='" + newPassword + "' where id=" + id + " and password='" + oldPassword + "'") == 1);
	}
	public static boolean verifiedEmail(int id){
		return (Database.executeUpdate("update user set verify=1,notify=true where id=" + id + " and verify=0") == 1);
	}
	public static boolean createPhone(User user, String phone, String code){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			rs = stmt.executeQuery("select id from user where phone='" + phone + "'");
			if(rs.next()){
				return false;
			}
			int count;
			rs = stmt.executeQuery("select count from unverified_phone where id=" + user.id);
			if(rs.next()){
				count = rs.getInt("count");
			}else{
				count = 0;
			}
			if(count == 0){
				stmt.executeUpdate("insert into unverified_phone(id,phone,code,time) values(" + user.id + ",'" + phone + "','" + code + "',now())");
				con.commit();
			}else if(count < 3){
				stmt.executeUpdate("update unverified_phone set phone='" + phone + "',code='" + code + "',time=now(),count=count+1 where id=" + user.id);
				if(count == 2){
					stmt.executeUpdate("update user set phone='0' where id=" + user.id);
				}
				con.commit();
				// 更新 Session 中的物件
				user.phone = "0";
			}else{ // 不可重複驗證超過三次
				return false;
			}
			return true;
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	public static boolean verifiedPhone(User user, String code){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			String phone;
			rs = stmt.executeQuery("select phone from unverified_phone where id=" + user.id + " and code='" + code + "' and TO_SECONDS(now()) - TO_SECONDS(time) < 3600");
			if(rs.next()){
				phone = rs.getString("phone");
			}else{
				return false;
			}
			// 送百元體驗金活動
			stmt.executeUpdate("update user set money=money+100,phone='" + phone + "',verify=2 where id=" + user.id + " and verify=1");
			stmt.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + user.id + ",100,''," + Transaction.TYPE_EVENT + "," + Transaction.STATE_COMPLETED + ",now())");
			//stmt.executeUpdate("update user set phone='" + phone + "',verify=2 where id=" + user.id + " and verify=1");
			con.commit();
			// 更新 Session 中的物件
			user.money = user.money + 100; // 送百元體驗金活動
			user.phone = phone;
			user.verify = 2;
			return true;
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 取得我的關注列表
	public static int[] getFavorites(int id){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			int[] favorites = new int[User.MAX_FAVORITE];
			int index = 0;
			rs = stmt.executeQuery("select * from favorite where id=" + id);
			while(rs.next()){
				favorites[index] = rs.getInt("user");
				index++;
			}
			return favorites;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 把使用者加入我的關注列表，最多 20 筆
	public static boolean addFavorite(int id, int user){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			rs = stmt.executeQuery("select count(*) from favorite where id=" + id);
			rs.next();
			if(rs.getInt("count(*)") >= User.MAX_FAVORITE){
				return false;
			}
			stmt.executeUpdate("insert ignore into favorite(id,user) values(" + id + "," + user + ")");
			con.commit();
			return true;
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 把使用者從我的關注列表移除
	public static boolean deleteFavorite(int id, int user){
		return (Database.executeUpdate("delete from favorite where id=" + id + " and user=" + user) != -1);
	}
	// 更改使用者的通知設定
	public static boolean changeNotify(User user, boolean notify){
		if(Database.executeUpdate("update user set notify=" + notify + " where id=" + user.id) == 1){
			user.notify = notify;
			return true;
		}else{
			return false;
		}
	}
	/* Instance Definition */
	public int id;
	public String email;
	public String name;
	public String phone;
	public int verify;
	public boolean notify;
	public int money, fee;
	public Date loginTime, joinTime;
	public User(int id, String email, String name, String phone, int verify, boolean notify, int money, int fee, Date loginTime, Date joinTime){
		this.id = id;
		this.email = email;
		this.name = name;
		this.phone = phone;
		this.verify = verify;
		this.notify = notify;
		this.money = money;
		this.fee = fee;
		this.loginTime = loginTime;
		this.joinTime = joinTime;
	}
}